package chase.myrewards;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map.Entry;

import chase.myrewards.beans.Customer;

/**
 * This class holds static string fields that are all the queries we make to the database.
 * It's purpose is to make updated and modifying the queries easy and extendible.
 * 
 * @author Meena Abdou
 * @date 04/30/2013
 */
public final class Queries {
	
	// Changed the query, now it returns sum of used, previous and recently accrued points for each account
	//Works for demo data
	public static String ACCOUNT_INFO(String num, String id){
		return "SELECT sum(balances.previous), sum(balances.recently_accrued), sum(balances.used),"+ 
				      "account.account_num," +
				      "account.account_type," +
				      "account.cycle_num " +
				"FROM balances JOIN account ON balances.account_num = account.account_num " + 
				"WHERE account.account_num ='" + num + "' AND account.customer_id='" + id + "'";
	}
	
	//Works for demo data
	public static String ACCOUNT_LIST(String id){
		return "SELECT account_num, account_type " +
			   "FROM account " +
			   "WHERE customer_id ='" + id + "'";
	}
	

	// Works on demo data
	//May 10th deleted address,lat, lon from query
	public static String CATEGORY(String mcc){
		return "SELECT DISTINCT category " +
			   "FROM geocode " +
			   "WHERE mcc = '" + mcc + "'";
	}
	
	//Works for demo data
	public static String CUSTOMER(String password){
		return "SELECT * " +
			   "FROM settings " +
			   "WHERE password ='" + password + "'";
	}
	
	//Works for demo data
	public static String CUSTOMER_UPDATE(Customer cust){
		return "UPDATE settings " +
			   "SET password = '" + cust.getPassword() + "', " + 
			       "show_locations = '" + (cust.getShowLocations() ? "1" : "0") + "', " + 
			       "show_favorites = '" + (cust.getFavorites() ? "1" : "0") + "', " + 
			       "show_recent = '" + (cust.getRecent() ? "1" : "0") + "' " + 
			   "WHERE customer_id = '" + cust.getCustomerID() + "'"; 
	}
	
	// Works on demo data
	public static String DESCRIPTION_MCC_PROGRAM(String programid){
		return "SELECT program.description, program_mcc.mcc " +
			   "FROM program JOIN program_mcc ON program.program_id = program_mcc.program_id " +
			   "WHERE program.program_id = '" + programid + "'";
	}
	
	// Works on demo data
	//query to get description and mcc for a promoid
	public static String DESCRIPTION_MCC_PROMO( String promoid){
		return "SELECT promotion.description, promo_mcc.mcc " +
			   "FROM promotion JOIN promo_mcc ON promotion.promo_id = promo_mcc.promo_id  " +
			   "WHERE promotion.promo_id = '" + promoid + "'";
	}
	
	// Works for demo data
	public static String FAVORITE_LOCATIONS(String num){
		return "SELECT favorites.name " + 
		       "FROM favorites JOIN geocode ON favorites.address = geocode.address " +
		       "WHERE favorites.account_num = '" + num + "'";
	}
	
	public static String PLACES_QUERY(String lat, String lon, HashMap<String, String> params){
		StringBuffer sbuff = new StringBuffer();
		sbuff.append("https://maps.googleapis.com/maps/api/place/nearbysearch/xml?");
		sbuff.append("location=" + lat + "," + lon + "&");
		sbuff.append("radius=8046.72&"); // 5 mile radius
		sbuff.append("sensor=false&");   // Servlet does not have GPS so it's always false
		
		Iterator<Entry<String, String>> it = params.entrySet().iterator();
		while(it.hasNext()){
			Entry<String, String> entry = (Entry<String, String>) it.next();
			sbuff.append((String)entry.getKey() + "=" + (String)entry.getValue() + "&");
		}
		sbuff.append("key=AIzaSyAA3FT3YH5YJlA8BLW48KP4Rq8kma5PAdA"); // Browser key
		
		return sbuff.toString();
	}
	
	// Works on demo data
	public static String PROGRAM_ID(String num){
		return "SELECT program_id FROM account_program WHERE account_num ='" + num + "'";
	}
	
	//Works for demo data
	public static String PROGRAM_ID_BALANCES(String num, String id){
		return "SELECT balances.program_id FROM balances " +
				"JOIN account ON balances.account_num = account.account_num " +
				"WHERE balances.account_num ='" + num + "' AND account.customer_id='" + id + "'";
	}
	
	// Works on demo data
	public static String PROMO_ID(String num){
		return "SELECT promo_id, eligible " +
			   "FROM eligible " +
			   "WHERE account_num ='" + num + "'";
	}
	
	// Works on demo data
	public static String RECENT_LOCATIONS(String num){
		return "SELECT transaction.store_name FROM transaction " +
			   "JOIN geocode ON transaction.address = geocode.address " +
			   "WHERE transaction.account_num ='" + num + "'";
	}
	
	// Works for demo data
	// get sum all recently accrued points for one program id
	public static String RECENTLY_ACCRUED(String program_id, String num){
		return "SELECT balances.recently_accrued,program.description " +
			   "FROM balances JOIN program ON balances.program_id = program.program_id " +
			   "WHERE balances.program_id ='" + program_id + "' AND balances.account_num ='" + num + "'";
	}
	
	// Works on demo data
	// Shows redemption channels for each program id associated with the account number
	public static String REDEMPTION_CHANNELS(String num){
		return "SELECT program.program_id, program.phone, program.web, program.store, program.card " +
			   "FROM program JOIN account_program ON program.program_id = account_program.program_id " +
			   "WHERE account_program.account_num = '"+ num +"'";
	}
	
	// Works on demo data
	public static String REWARDS_AVAILABLE(String num, String id){
		return "SELECT rewards_available " +
			   "FROM account " +
			   "WHERE account_num ='" + num + "' AND customer_id='" + id + "'";
	}
	
	//Works for demo data
	public static String REWARDS_USAGE(String num, String id){
		return "SELECT rewards_usage.used_points, rewards_usage.date, rewards_usage.description " +
			   "FROM rewards_usage JOIN account ON rewards_usage.account_num = account.account_num  " +
			   "WHERE account.account_num ='" + num + "' AND account.customer_id='" + id + "'";
	}
	
}
